SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/** Create Stored Procedures **/
CREATE PROCEDURE [dbo].[DnnProduct_CDS_RequestsReport]
	@DistributorID VARCHAR(50),
	@StartDate DATETIME,
	@StopDate DATETIME
AS
BEGIN
	SET NOCOUNT ON

    DECLARE @SQLString NVARCHAR(3000)
	DECLARE @ParmDefinition NVARCHAR(200)

	

	SET @StopDate = DATEADD(day,1,@StopDate)

	SET @SQLString = N'WITH CTE (RedemptionCode, DownloadUrl, DateTime, CountOfUse) AS  
						(  
							SELECT DISTINCT 
								rc.RedemptionCode
								,rc.DownloadUrl
								,ISNULL(
											(SELECT MIN(dot1.DateTime) FROM DISC_DistOrderItems dot1 WITH (NOLOCK) WHERE dot1.ID = dot.ID), 
											null
										) as x
								,ISNULL(
											(
												SELECT 1 WHERE EXISTS (SELECT * FROM DISC_Fulfillment f WITH (NOLOCK) WHERE dot.DistID = f.DistID AND dot.OrderID = f.OrdersID AND dot.ID = f.OrdersItemID)
											), 
											0
										) AS y
							FROM DnnProduct_CDS_RedemptionCodes rc WITH (NOLOCK) 
									JOIN DISC_DistOrderItems dot WITH (NOLOCK) 
									ON (rc.DownloadUrl = dot.DownloadLink) AND (dot.DistID = @DistributorID) AND (dot.DateTime BETWEEN @StartDate AND @StopDate)
							WHERE rc.DownloadUrl LIKE ''%download.aspx?PID=DistID%''  

							UNION ALL  

							SELECT DISTINCT 
								RedemptionCode
								,DownloadUrl
								,ISNULL(
											(SELECT MIN(dot1.DateTime) FROM DISC_DistOrderItems dot1 WITH (NOLOCK) WHERE dot1.ID = dot.ID), 
											null
										) as x
								,ISNULL(
											(
												SELECT 1 WHERE EXISTS 
												(
													SELECT * 
													FROM adept..distributor ad  WITH (NOLOCK) 
														JOIN adept..fulfillment af WITH (NOLOCK) 
														ON af.DistID = ad.DistID AND af.Confirmed = ''T'' AND af.TransID = dbo.[GetACSTransactionId](rc.DownloadUrl)
														JOIN adept..fulfillmentitem afi  WITH (NOLOCK) 
														ON afi.FulfillmentID = af.FulfillmentID
														JOIN adept..license al   WITH (NOLOCK) 
														ON al.UserID = af.UserID AND al.ResourceID = afi.ResourceID
													WHERE ad.DistUrl = dc.DistUrl
												)
											),  
											0 
										) AS y
							FROM DnnProduct_CDS_RedemptionCodes rc WITH (NOLOCK) 
									JOIN DISC_DistOrderItems dot WITH (NOLOCK)   
									ON (rc.DownloadUrl = dot.DownloadLink) AND (dot.DistID = @DistributorID) AND (dot.DateTime BETWEEN @StartDate AND  @StopDate)
									JOIN DISC_DistributorConfig dc  WITH (NOLOCK) 
									ON dc.DistID = dot.DistID
							WHERE rc.DownloadUrl LIKE ''%URLLink.acsm?action=enterorder%''  
						)  
						SELECT   
							RedemptionCode,   
							DownloadUrl,   
							DateTime,  
							CountOfUse,  
							(CASE WHEN CountOfUse > 0 THEN ''Y'' ELSE ''N'' END) AS CodeRedeemded  
						FROM CTE   
						ORDER BY [DateTime] DESC, RedemptionCode ASC'

	SET @ParmDefinition = N'@DistributorID VARCHAR(50), @StartDate DATETIME, @StopDate DATETIME'
	EXECUTE sp_executesql @SQLString, @ParmDefinition, @DistributorID, @StartDate, @StopDate



END
GO
